﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.IO;
using System.Net;
using System.Data.SqlClient;
using WinServer;


namespace DBUtility
{
    public class MYSQLHelper
    {
        public static string connString = "User Id=root;Password=123456;Host=127.0.0.1;Database=mysql;Charset=gb2312";
        public static int Update(string sql)
        {

            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "调用 public static int Update(string sql)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }

        public static void Update(byte[] saveBytes)
        {
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connString))
                {
                    connection.Open();
                    string sql = $"INSERT INTO {ConstDefine.UseRecordTableName} (data) VALUES (@data)";
                    using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        command.Parameters.AddWithValue("@data", saveBytes);
                        command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
        }

        public static List<byte[]> GetData(int id)
        {
            List<byte[]> result = new List<byte[]>();
            try
            {
                using (MySqlConnection connection = new MySqlConnection(connString))
                {
                    connection.Open();
                    string sql = $"SELECT data FROM {ConstDefine.UseRecordTableName}";
                    using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                result.Add((byte[])reader["data"]);
                            }
                        }
                    }
                   
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        #region 单一结果
        public static object SingleResult(string sql)
        {

            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "调用  public static object SingleResult(string sql)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region 查询
        public static MySqlDataReader getReader(string sql)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string info = "调用public static  MySqlDataReader getReader(string sql)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }

        }
        #endregion
        #region 编写带参数的sql语句的方法
        public static int Update(string sql, MySqlParameter[] param)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "调用 public static int Update(string sql, MySqlParameter[] param)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region 单一结果
        public static object SingleResult(string sql, MySqlParameter[] param)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "调用public static object SingleResult(string sql, MySqlParameter[] param)";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region 查询
        public static MySqlDataReader getReader(string sql, MySqlParameter[] param)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string info = "调用public static MySqlDataReader getReader(string sql, MySqlParameter[] param)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }

        }
        #endregion
        #region 存储过程

        public static int UpdateByProcedure(string spname, MySqlParameter[] param)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(spname, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                string info = "调用 UpdateByProcedure出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region
        public static object SingleResultByProcedure(string spname, MySqlParameter[] param)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(spname, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                string info = "调用public static object SingleResult(string sql, MySqlParameter[] param)";
                WriteLog(ex.Message);
                throw new Exception(info);
            }
            finally
            {
                conn.Close();

            }
        }
        #endregion
        #region 查询
        public static MySqlDataReader getReaderByProcedure(string spname, MySqlParameter[] param)
        {
            MySqlConnection conn = new MySqlConnection(connString);
            MySqlCommand cmd = new MySqlCommand(spname, conn);
            try
            {
                conn.Open();
                cmd.Parameters.AddRange(param);
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                string info = "调用public static MySqlDataReader getReader(string sql, MySqlParameter[] param)出现错误";
                WriteLog(ex.Message);
                throw new Exception(info);
            }

        }
        #endregion
        /// <summary>
        /// 系统日志
        /// </summary>
        /// <param name="log"></param>
        public static void WriteLog(string log)
        {
            FileStream fs = new FileStream("mysqlhelp.log", FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);
            sw.WriteLine(DateTime.Now.ToString() + "错误信息：" + log);
            sw.Close();
            fs.Close();
        }
    }
}

